package io.openaev.migration;

import java.sql.Statement;
import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.springframework.stereotype.Component;

@Component
public class V4_21__Add_vulnerability_fk_on_injector_contracts extends BaseJavaMigration {

  @Override
  public void migrate(Context context) throws Exception {
    try (Statement statement = context.getConnection().createStatement()) {
      // re-index injects, simulations and scenarios in ES
      statement.executeUpdate(
          """
        CREATE TABLE injectors_contracts_vulnerabilities (
            injector_contract_id VARCHAR(255) NOT NULL REFERENCES injectors_contracts(injector_contract_id) ON DELETE CASCADE,
            vulnerability_id VARCHAR(255) NOT NULL REFERENCES cves(cve_id) ON DELETE CASCADE,
            PRIMARY KEY (injector_contract_id, vulnerability_id));
        CREATE INDEX idx_injectors_contracts_vulnerabilities_vuln ON injectors_contracts_vulnerabilities (vulnerability_id);
        CREATE INDEX idx_injectors_contracts_vulnerabilities_contract ON injectors_contracts_vulnerabilities (injector_contract_id);

        -- Trigger for AFTER DELETE
        CREATE TRIGGER after_delete_update_injector_contract_updated_at
            AFTER DELETE ON public.injectors_contracts_vulnerabilities
            FOR EACH ROW
        EXECUTE FUNCTION update_injector_contract_updated_at();

        -- Trigger for AFTER INSERT
        CREATE TRIGGER after_insert_update_injector_contract_updated_at
            AFTER INSERT ON public.injectors_contracts_vulnerabilities
            FOR EACH ROW
        EXECUTE FUNCTION update_injector_contract_updated_at();
        """);
    }
  }
}
